package com.show.service.org;


import com.show.dao.sys_org.SysOrgRepositoty;
import com.show.dto.sys_org.SysOrg;
import com.show.dto.sys_role.Ztree;
import com.show.util.QueryUtil;
import com.show.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

/**
 * 部门service
 *
 * @author 姜文
 * @Param
 * @date 2018年1月30日, PM 04:38:56
 */
@Service
@Transactional
public class SysOrgService {

    @Autowired
    private QueryUtil queryUtil;
    @Autowired
    private SysOrgRepositoty sysOrgRepositoty;

    /**
     * 查询所有部门信息转换ztree格式
     *
     * @author 姜文
     * @Param
     * @date 2018年1月30日, PM 04:39:06
     */
    public List<Ztree> findParentToChirld() {
        String sql = "SELECT org_id as id,parent_org_id as pId,org_name as name FROM sys_org where sts=1";
        List<Ztree> ztrees = queryUtil.query(sql, Ztree.class);
        for (Ztree z : ztrees) {
            if (z.getpId() == null || z.getpId().equals("")) {
                z.setpId("0");
            }
        }
        return ztrees;
    }

    /**
     * 根据empId查询orgId
     *
     * @author 姜文
     * @Param
     * @date 2018年2月26日, PM 03:36:01
     */
    public String getOrgIdByEmpId(String empId) {
        String sql = "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t`sys_org` so\n" +
                "LEFT JOIN sys_emp_org seo ON seo.org_id = so.org_id\n" +
                "LEFT JOIN sys_emp se ON seo.emp_id = se.emp_id\n" +
                "WHERE\n" +
                "\tse.emp_id = '" + empId + "'";
        List<SysOrg> sysOrgList = queryUtil.query(sql, SysOrg.class);
        String orgIds = "";
        for (int i = 0; i < sysOrgList.size(); i++) {
            if (sysOrgList.size() - 1 == i) {
                orgIds += sysOrgList.get(i).getOrgId();
            } else {
                orgIds += sysOrgList.get(i).getOrgId() + ",";
            }
        }
        return orgIds;
    }

    /**
     * 查询所有社区
     *
     * @author yxd
     * @Param
     * @date 2018年1月30日
     */
    public List<SysOrg> findAllDeparts() {
        String sql = "select org_id,org_name from sys_org where org_flag=2 and sts=1";
        return queryUtil.query(sql, SysOrg.class);
    }

    /**
     * @param orgId 部门id
     * @return
     * @author liukai
     */
    public List<SysOrg> findDeparts(String orgId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT org_id, org_name FROM sys_org WHERE org_flag = 2 AND sts = 1");
        if (StringUtil.isNotEmpty(orgId)) {
            sql.append(" AND org_id = '").append(orgId).append("'");
        }
        return queryUtil.query(sql.toString(), SysOrg.class);
    }

    /**
     * 查询部门ByID
     *
     * @author yxd
     * @Param
     * @date 2018年1月30日
     */
    public SysOrg findOne(int orgId) {
        return sysOrgRepositoty.findOne(orgId);
    }


    /**
     * 查询所有街道
     *
     * @author liukai
     */
    public List<SysOrg> findAllStreet() {
        String sql = "SELECT * FROM sys_org WHERE org_flag = 1 AND sts = '1'";
        return queryUtil.query(sql, SysOrg.class);
    }


    /**
     * 查找街道下面的社区
     *
     * @param streetId 街道id
     * @author liukai
     */
    public List<SysOrg> findCommunity(String streetId) {
        String sql = "SELECT * FROM sys_org AS org WHERE org.parent_org_id = '" + streetId + "'";
        return queryUtil.query(sql, SysOrg.class);
    }

    public Integer getParentOrgId(String orgId) {
        String sql = "SELECT * FROM `sys_org` where org_id = " + orgId;
        return queryUtil.query(sql, SysOrg.class).get(0).getParentOrgId();
    }

    /**
     * 根据orgIds 查询所在部门信息
     *
     * @param orgIds
     * @return
     */
    public List<SysOrg> findOrgByOrgIds(String orgIds) {
        String sql = "SELECT * FROM `sys_org` where org_id in (" + orgIds + ") and org_flag =2 or parent_org_id in (" + orgIds + ")  ";
        return queryUtil.query(sql, SysOrg.class);
    }

    /**
     * 查询部门树
     *
     * @param
     * @author 顾志杰
     * @date 2018/2/2 9:11
     */
    public List<Ztree> OrgTree() {
        String sql = "select org_id id,org_name name,parent_org_id pid from sys_org where sts=1";
        return queryUtil.query(sql, Ztree.class);
    }

    /**
     * 查询单个部门信息
     *
     * @param orgId 部门id
     * @author 顾志杰
     * @date 2018/2/2 9:14
     */
    public SysOrg FindOneOrg(Integer orgId) {
        return sysOrgRepositoty.findOne(orgId);
    }

    /**
     * 添加部门
     *
     * @param sysOrg 部门类
     * @author 顾志杰
     * @date 2018/2/2 9:11
     */
    public String InsertOrg(SysOrg sysOrg) {
        if (sysOrgRepositoty.findByOrgNameAndSts(sysOrg.getOrgName(), 1).size() != 0) {
            return "部门名重复";
        } else if (sysOrg.getOrgName() == sysOrg.getParentOrgName()) {
            return "父级部门与子级部门重复";
        } else {
            try {
                if (sysOrg.getOrgId() != null) {
                    sysOrg.setOrgId(null);
                }
                if (sysOrg.getParentOrgId() == null) {
                    sysOrg.setOrgFlag(1);
                } else {
                    sysOrg.setOrgFlag(sysOrgRepositoty.findOne(sysOrg.getParentOrgId()).getOrgFlag() + 1);
                }
                ;
                sysOrg.setSts(1);
                Date date = new Date();
                sysOrg.setCdate(date);
                sysOrg.setUpdDate(date);
                if (sysOrg.getParentOrgId() == null) {
                    sysOrg.setParentOrgId(0);
                }
                sysOrgRepositoty.save(sysOrg);
                return "添加部门成功";
            } catch (Exception e) {
                return "添加部门失败";
            }
        }
    }

    /**
     * 删除部门
     *
     * @param orgId 部门id
     * @author 顾志杰
     * @date 2018/2/2 9:28
     */
    public String DeleteOrg(Integer orgId) {
        List<SysOrg> orgList = sysOrgRepositoty.getOrg(orgId); //查询部门表
        int num = sysOrgRepositoty.getEmp(orgId);         //查询人员表
        int num1 = sysOrgRepositoty.getGrids(orgId);     //查询网格表
        int p1 = sysOrgRepositoty.getPopulations(orgId); //查询人口表
        int p2 = sysOrgRepositoty.getDb(orgId);          //查询低保人员
        int p3 = sysOrgRepositoty.getDisabled(orgId);    //查询残疾人员
        int p4 = sysOrgRepositoty.getSanWu(orgId);       //查询三无人员
        int p5 = sysOrgRepositoty.getSpecial(orgId);     //查询特殊人员
        int p6 = sysOrgRepositoty.getWB(orgId);          //查询五保人员
        int p7 = sysOrgRepositoty.getLeftOverChildren(orgId);//查询孤儿信息
        int p8 = sysOrgRepositoty.getLowComeIn(orgId);   //查询低收入人员

        if (orgList.size() != 0) {
            return "该部门有下级部门，请清空下级部门再执行操作";
        } else if (num > 0) {
            return "该部门下尚有使用人员，请勿删除";
        } else if (num1 > 0) {
            return "该部门下尚有网格，请勿删除";
        } else if (p1 > 0 || p2 > 0 || p3 > 0 || p4 > 0 || p5 > 0 || p6 > 0 || p7 > 0 || p8 > 0) {
            return "该部门下尚有人口信息";
        } else {
            try {
                SysOrg sysOrg = sysOrgRepositoty.findOne(orgId);
                sysOrg.setSts(0);
                sysOrgRepositoty.saveAndFlush(sysOrg);
                return "删除成功";
            } catch (Exception e) {
                return "删除失败";
            }
        }
    }

    /**
     * 修改部门
     *
     * @param sysOrg 部门类
     * @author 顾志杰
     * @date 2018/2/2 10:16
     */
    public String UpdOrg(SysOrg sysOrg) {
        if (sysOrgRepositoty.findByOrgNameAndSts(sysOrg.getOrgName(), 1).size() != 0) {
            return "部门名重复";
        } else if (sysOrg.getOrgName() == sysOrg.getParentOrgName()) {
            return "父级部门与子级部门重复";
        } else {
            try {
                SysOrg sysOrg1 = sysOrgRepositoty.findOne(sysOrg.getOrgId());
                sysOrg1.setOrgName(sysOrg.getOrgName());
                Date date = new Date();
                sysOrg.setUpdDate(date);
                sysOrgRepositoty.saveAndFlush(sysOrg1);
                return "修改部门成功";
            } catch (Exception e) {
                return "修改部门失败";
            }
        }
    }

    /**
     * 查询可上报的街道集合
     * 用于社区继续上报事件选择
     *
     * @return
     */
    public List<SysOrg> ChooseTheStreet() {
        String sql = "SELECT * FROM `sys_org` where sts = '1' AND parent_org_id = 0";
        return queryUtil.query(sql, SysOrg.class);
    }

    /**
     * 查询可上报的街道集合 for Tree
     * 用于社区继续上报事件选择
     *
     * @return
     */
    public List<SysOrg> ChooseTheStreetForTree() {
        String sql = "SELECT org_id AS orgId, org_name AS orgName FROM `sys_org` where parent_org_id = 0 AND sts = '1'";
        return queryUtil.query(sql, SysOrg.class);
    }

    /**
     * 根据当前登陆人ID获取当前登陆人所在街道（部门）可下派的社区
     *
     * @param orgId
     * @return
     */
    public List<SysOrg> ChooseTheCommunity(Integer orgId) {
        String sql = "SELECT * from sys_org where sts = '1' AND parent_org_id = " + orgId;
        return queryUtil.query(sql, SysOrg.class);
    }

    /**
     * 根据当前登陆人ID获取当前登陆人所在街道（部门）可下派的社区
     *
     * @param orgId
     * @return
     */
    public List<SysOrg> ChooseTheCommunityForTree(Integer orgId) {
        String sql = "SELECT org_id AS orgId, org_name AS orgName from sys_org where sts = '1' AND parent_org_id = " + orgId;
        return queryUtil.query(sql, SysOrg.class);
    }

}
